"""数据同步工具模块 - 提供SQL Server到SQLite的定时同步功能。"""

import sqlite3
import pyodbc
import schedule
import time
import threading
from datetime import datetime, timedelta
from typing import Dict, List, Optional, Any
from dataclasses import dataclass
from langchain_core.tools import tool
from pydantic import BaseModel, Field
from utils.logger import logger
from utils.config import Config


@dataclass
class SyncConfig:
    """同步配置类"""
    sql_server_host: str
    sql_server_database: str
    sql_server_username: str
    sql_server_password: str
    sqlite_path: str = "Chinook.db"
    sync_interval_minutes: int = 30
    tables_to_sync: List[str] = None
    batch_size: int = 1000
    
    def __post_init__(self):
        if self.tables_to_sync is None:
            self.tables_to_sync = []


class DataSyncTool:
    """数据同步工具类"""
    
    def __init__(self, config: SyncConfig):
        self.config = config
        self.is_running = False
        self.sync_thread = None
        self.last_sync_time = None
        
    def get_sql_server_connection(self):
        """获取SQL Server连接"""
        try:
            connection_string = (
                f"DRIVER={{ODBC Driver 17 for SQL Server}};"
                f"SERVER={self.config.sql_server_host};"
                f"DATABASE={self.config.sql_server_database};"
                f"UID={self.config.sql_server_username};"
                f"PWD={self.config.sql_server_password};"
            )
            return pyodbc.connect(connection_string)
        except Exception as e:
            logger.error(f"连接SQL Server失败: {e}")
            raise
    
    def get_sqlite_connection(self):
        """获取SQLite连接"""
        try:
            return sqlite3.connect(self.config.sqlite_path)
        except Exception as e:
            logger.error(f"连接SQLite失败: {e}")
            raise
    
    def get_table_schema(self, table_name: str, sql_server_conn) -> List[Dict]:
        """获取表结构信息"""
        cursor = sql_server_conn.cursor()
        cursor.execute(f"""
            SELECT COLUMN_NAME, DATA_TYPE, IS_NULLABLE, CHARACTER_MAXIMUM_LENGTH
            FROM INFORMATION_SCHEMA.COLUMNS
            WHERE TABLE_NAME = '{table_name}'
            ORDER BY ORDINAL_POSITION
        """)
        
        columns = []
        for row in cursor.fetchall():
            columns.append({
                'name': row[0],
                'type': row[1],
                'nullable': row[2] == 'YES',
                'max_length': row[3]
            })
        return columns
    
    def convert_sql_server_type_to_sqlite(self, sql_server_type: str) -> str:
        """将SQL Server数据类型转换为SQLite数据类型"""
        type_mapping = {
            'int': 'INTEGER',
            'bigint': 'INTEGER',
            'smallint': 'INTEGER',
            'tinyint': 'INTEGER',
            'bit': 'INTEGER',
            'decimal': 'REAL',
            'numeric': 'REAL',
            'float': 'REAL',
            'real': 'REAL',
            'money': 'REAL',
            'smallmoney': 'REAL',
            'varchar': 'TEXT',
            'nvarchar': 'TEXT',
            'char': 'TEXT',
            'nchar': 'TEXT',
            'text': 'TEXT',
            'ntext': 'TEXT',
            'datetime': 'DATETIME',
            'datetime2': 'DATETIME',
            'smalldatetime': 'DATETIME',
            'date': 'DATE',
            'time': 'TIME',
            'timestamp': 'BLOB',
            'binary': 'BLOB',
            'varbinary': 'BLOB',
            'image': 'BLOB',
            'uniqueidentifier': 'TEXT'
        }
        return type_mapping.get(sql_server_type.lower(), 'TEXT')
    
    def create_sqlite_table(self, table_name: str, columns: List[Dict], sqlite_conn):
        """在SQLite中创建表"""
        cursor = sqlite_conn.cursor()
        
        # 检查表是否存在
        cursor.execute(f"""
            SELECT name FROM sqlite_master 
            WHERE type='table' AND name='{table_name}'
        """)
        
        if cursor.fetchone():
            logger.info(f"表 {table_name} 已存在，跳过创建")
            return
        
        # 构建CREATE TABLE语句
        column_definitions = []
        for col in columns:
            sqlite_type = self.convert_sql_server_type_to_sqlite(col['type'])
            nullable = "" if col['nullable'] else "NOT NULL"
            column_definitions.append(f"`{col['name']}` {sqlite_type} {nullable}")
        
        create_sql = f"""
            CREATE TABLE IF NOT EXISTS `{table_name}` (
                id INTEGER PRIMARY KEY AUTOINCREMENT,
                {', '.join(column_definitions)},
                sync_timestamp DATETIME DEFAULT CURRENT_TIMESTAMP
            )
        """
        
        cursor.execute(create_sql)
        sqlite_conn.commit()
        logger.info(f"表 {table_name} 创建成功")
    
    def sync_table_data(self, table_name: str, sql_server_conn, sqlite_conn):
        """同步单个表的数据"""
        try:
            # 获取表结构
            columns = self.get_table_schema(table_name, sql_server_conn)
            if not columns:
                logger.warning(f"表 {table_name} 不存在或无列信息")
                return
            
            # 创建SQLite表
            self.create_sqlite_table(table_name, columns, sqlite_conn)
            
            # 获取SQL Server数据
            sql_server_cursor = sql_server_conn.cursor()
            column_names = [col['name'] for col in columns]
            
            # 分批查询数据
            offset = 0
            total_synced = 0
            
            while True:
                query = f"""
                    SELECT {', '.join(column_names)}
                    FROM {table_name}
                    ORDER BY (SELECT NULL)
                    OFFSET {offset} ROWS
                    FETCH NEXT {self.config.batch_size} ROWS ONLY
                """
                
                sql_server_cursor.execute(query)
                rows = sql_server_cursor.fetchall()
                
                if not rows:
                    break
                
                # 插入到SQLite
                sqlite_cursor = sqlite_conn.cursor()
                
                # 清空目标表（可选，根据需求调整）
                if offset == 0:
                    sqlite_cursor.execute(f"DELETE FROM `{table_name}`")
                
                # 构建插入语句
                placeholders = ', '.join(['?' for _ in column_names])
                insert_sql = f"""
                    INSERT INTO `{table_name}` ({', '.join([f'`{col}`' for col in column_names])})
                    VALUES ({placeholders})
                """
                
                # 批量插入
                sqlite_cursor.executemany(insert_sql, rows)
                sqlite_conn.commit()
                
                total_synced += len(rows)
                offset += self.config.batch_size
                
                logger.info(f"表 {table_name} 已同步 {total_synced} 行数据")
            
            logger.info(f"表 {table_name} 同步完成，总计 {total_synced} 行")
            
        except Exception as e:
            logger.error(f"同步表 {table_name} 失败: {e}")
            raise
    
    def perform_sync(self):
        """执行一次完整的数据同步"""
        try:
            logger.info("开始数据同步...")
            start_time = datetime.now()
            
            # 建立连接
            with self.get_sql_server_connection() as sql_server_conn, \
                 self.get_sqlite_connection() as sqlite_conn:
                
                # 如果没有指定表，获取所有表
                if not self.config.tables_to_sync:
                    cursor = sql_server_conn.cursor()
                    cursor.execute("""
                        SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES
                        WHERE TABLE_TYPE = 'BASE TABLE'
                    """)
                    self.config.tables_to_sync = [row[0] for row in cursor.fetchall()]
                
                # 同步每个表
                for table_name in self.config.tables_to_sync:
                    logger.info(f"正在同步表: {table_name}")
                    self.sync_table_data(table_name, sql_server_conn, sqlite_conn)
            
            self.last_sync_time = datetime.now()
            duration = self.last_sync_time - start_time
            logger.info(f"数据同步完成，耗时: {duration}")
            
        except Exception as e:
            logger.error(f"数据同步失败: {e}")
            raise
    
    def start_scheduled_sync(self):
        """启动定时同步"""
        if self.is_running:
            logger.warning("定时同步已在运行中")
            return
        
        self.is_running = True
        
        # 设置定时任务
        schedule.every(self.config.sync_interval_minutes).minutes.do(self.perform_sync)
        
        def run_scheduler():
            while self.is_running:
                schedule.run_pending()
                time.sleep(1)
        
        self.sync_thread = threading.Thread(target=run_scheduler, daemon=True)
        self.sync_thread.start()
        
        logger.info(f"定时同步已启动，间隔: {self.config.sync_interval_minutes} 分钟")
    
    def stop_scheduled_sync(self):
        """停止定时同步"""
        self.is_running = False
        schedule.clear()
        
        if self.sync_thread and self.sync_thread.is_alive():
            self.sync_thread.join(timeout=5)
        
        logger.info("定时同步已停止")
    
    def get_sync_status(self) -> Dict[str, Any]:
        """获取同步状态"""
        return {
            'is_running': self.is_running,
            'last_sync_time': self.last_sync_time.isoformat() if self.last_sync_time else None,
            'sync_interval_minutes': self.config.sync_interval_minutes,
            'tables_to_sync': self.config.tables_to_sync,
            'sqlite_path': self.config.sqlite_path
        }


# 全局同步工具实例
_sync_tool_instance = None


class SyncToolInput(BaseModel):
    """同步工具输入参数"""
    action: str = Field(description="操作类型: start, stop, sync_once, status")
    sql_server_host: Optional[str] = Field(default=None, description="SQL Server主机地址")
    sql_server_database: Optional[str] = Field(default=None, description="SQL Server数据库名")
    sql_server_username: Optional[str] = Field(default=None, description="SQL Server用户名")
    sql_server_password: Optional[str] = Field(default=None, description="SQL Server密码")
    tables_to_sync: Optional[List[str]] = Field(default=None, description="要同步的表列表")
    sync_interval_minutes: Optional[int] = Field(default=30, description="同步间隔（分钟）")


@tool(args_schema=SyncToolInput)
def data_sync_tool(
    action: str,
    sql_server_host: Optional[str] = None,
    sql_server_database: Optional[str] = None,
    sql_server_username: Optional[str] = None,
    sql_server_password: Optional[str] = None,
    tables_to_sync: Optional[List[str]] = None,
    sync_interval_minutes: Optional[int] = 30
) -> str:
    """数据同步工具 - 用于从SQL Server同步数据到SQLite
    
    Args:
        action: 操作类型 (start: 启动定时同步, stop: 停止同步, sync_once: 执行一次同步, status: 查看状态)
        sql_server_host: SQL Server主机地址
        sql_server_database: SQL Server数据库名
        sql_server_username: SQL Server用户名
        sql_server_password: SQL Server密码
        tables_to_sync: 要同步的表列表，为空则同步所有表
        sync_interval_minutes: 同步间隔（分钟）
    
    Returns:
        str: 操作结果信息
    """
    global _sync_tool_instance
    
    try:
        if action == "status":
            if _sync_tool_instance:
                status = _sync_tool_instance.get_sync_status()
                return f"同步状态: {status}"
            else:
                return "同步工具未初始化"
        
        # 对于其他操作，需要配置信息
        if not all([sql_server_host, sql_server_database, sql_server_username, sql_server_password]):
            return "错误: 缺少必要的SQL Server连接信息"
        
        # 创建配置
        config = SyncConfig(
            sql_server_host=sql_server_host,
            sql_server_database=sql_server_database,
            sql_server_username=sql_server_username,
            sql_server_password=sql_server_password,
            tables_to_sync=tables_to_sync or [],
            sync_interval_minutes=sync_interval_minutes
        )
        
        # 初始化同步工具
        if not _sync_tool_instance:
            _sync_tool_instance = DataSyncTool(config)
        else:
            _sync_tool_instance.config = config
        
        if action == "start":
            _sync_tool_instance.start_scheduled_sync()
            return f"定时同步已启动，间隔: {sync_interval_minutes} 分钟"
        
        elif action == "stop":
            _sync_tool_instance.stop_scheduled_sync()
            return "定时同步已停止"
        
        elif action == "sync_once":
            _sync_tool_instance.perform_sync()
            return "数据同步执行完成"
        
        else:
            return f"未知操作: {action}。支持的操作: start, stop, sync_once, status"
    
    except Exception as e:
        logger.error(f"数据同步工具执行失败: {e}")
        return f"操作失败: {str(e)}"


# 便捷函数
def start_data_sync(config: SyncConfig) -> DataSyncTool:
    """启动数据同步服务"""
    sync_tool = DataSyncTool(config)
    sync_tool.start_scheduled_sync()
    return sync_tool


def stop_data_sync():
    """停止数据同步服务"""
    global _sync_tool_instance
    if _sync_tool_instance:
        _sync_tool_instance.stop_scheduled_sync()